package org.compiere.report;

import java.io.FileOutputStream;
import java.math.BigDecimal;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.Properties;
import java.util.logging.Level;

import org.adempiere.exceptions.AdempiereException;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.compiere.model.MAttachmentEntry;
import org.compiere.model.MBPartner;
import org.compiere.model.MDocType;
import org.compiere.model.MInvoice;
import org.compiere.model.MLocator;
import org.compiere.model.MOrg;
import org.compiere.model.X_A_Asset;
import org.compiere.process.ProcessInfo;
import org.compiere.process.ProcessInfoParameter;
import org.compiere.process.SvrProcess;
import org.compiere.util.DB;
import org.compiere.util.Env;
import org.compiere.util.Msg;

import extend.org.compiere.utils.Util;


public class A_Asset_Form_DA2 extends SvrProcess {

	/** Current context */
	private Properties m_ctx;
	/** Current transaction */
	private String m_trx;
	
	/** Process info    */
	private ProcessInfo pi;

	int p_AD_Org_ID;
	int p_A_Asset_ID;
	
	String getSQlresult = null;
	String tmpname = "A_Asset_Form_DA2.xlsx";
	/**
	 * Prepare - e.g., get Parameters.
	 */
	@Override
	protected void prepare() {
		log.info("");
		m_ctx = Env.getCtx();
		m_trx = get_TrxName();
		pi = getProcessInfo();
		ProcessInfoParameter[] para = getParameter();
		for (int i = 0; i < para.length; i++) {
			String name = para[i].getParameterName();
			if (para[i].getParameter() == null)
				;
//			else if (name.equals("AD_Org_ID")) 
//				p_AD_Org_ID  = para[i].getParameterAsInt();
			else if (name.equals("A_Asset_ID")) 
				p_A_Asset_ID  = para[i].getParameterAsInt();
			else
				log.log(Level.SEVERE, "Unknown Parameter: " + name);
		}

	}
	
	@Override
	protected String doIt() throws Exception {
		
		String ffn =Util.localFilePath+tmpname; // full file name
		int row1 = 28;

		MAttachmentEntry entry = Util.getAttachment(pi, m_ctx, ffn);
		if (entry == null)
			throw new AdempiereException(Msg.translate(m_ctx,"NotFoundTemplate"));

		Workbook workbook = new XSSFWorkbook(entry.getInputStream());
		Sheet sheet = workbook.getSheetAt(0);
		
		PreparedStatement pstmt = null;
		ResultSet rs = null;

		Timestamp currdate = new Timestamp(System.currentTimeMillis());
		X_A_Asset asset = new X_A_Asset (getCtx(), p_A_Asset_ID, get_TrxName());
		
		MOrg org = MOrg.get(m_ctx,asset.getAD_Org_ID());
		
		MBPartner bpartner = MBPartner.get(m_ctx, org.getLinkedC_BPartner_ID(get_TrxName()));

		
		Cell cell = null;

		cell = sheet.getRow(7).getCell(19); // Org name
	    cell.setCellValue(org.get_Translation("Name"));   
		
		cell = sheet.getRow(7).getCell(62); // IIN/BIN
	    cell.setCellValue(bpartner.getFI_BIN_IIN());
	    
	    MLocator locator = (MLocator) asset.getM_Locator();
	    int bpid = locator.get_ValueAsInt("C_BPartner_ID");
	    if (bpid>0) {
			MBPartner mol = MBPartner.get(m_ctx, bpid);

			cell = sheet.getRow(10).getCell(13); // MOL
		    cell.setCellValue(mol.getName());
			cell = sheet.getRow(57).getCell(30); // MOL
		    cell.setCellValue(mol.getName());

		    String sql = "SELECT (SELECT Name FROM HR_Department"
					+ " WHERE HR_Department_ID = HR_Employee.HR_Department_ID)"
				    + " FROM HR_Employee"
				    + " WHERE C_BPartner_ID = "+bpid 
				    + " AND startdate <= '"+currdate+"'"
				    + " AND enddate >= '"+currdate+"'";
			try
			{
				pstmt = DB.prepareStatement(sql.toString(), m_trx);
				rs = pstmt.executeQuery();
				while (rs.next())
				{
					cell = sheet.getRow(8).getCell(11); // MOL
				    cell.setCellValue(rs.getString(1));
				}
			}
			catch (Exception e)
			{
				log.log(Level.SEVERE, sql.toString(), e);
			}
			finally
			{
				DB.close(rs, pstmt);
			}
			

		    sql = "SELECT (SELECT Name FROM HR_Job"
					+ " WHERE HR_Job_ID = HR_Employee.HR_Job_ID)"
				    + " FROM HR_Employee"
				    + " WHERE C_BPartner_ID = "+bpid 
				    + " AND startdate <= '"+currdate+"'"
				    + " AND enddate >= '"+currdate+"'";
			try
			{
				pstmt = DB.prepareStatement(sql.toString(), m_trx);
				rs = pstmt.executeQuery();
				while (rs.next())
				{
					cell = sheet.getRow(57).getCell(11); // Post
				    cell.setCellValue(rs.getString(1));
				}
			}
			catch (Exception e)
			{
				log.log(Level.SEVERE, sql.toString(), e);
			}
			finally
			{
				DB.close(rs, pstmt);
			}
			
	    }
	    
	   
  
	    cell = sheet.getRow(13).getCell(65); // Current date
    	cell.setCellValue(new SimpleDateFormat("dd.MM.yyyy").format(currdate));

	    cell = sheet.getRow(15).getCell(24); // Asset's name
	    cell.setCellValue(asset.getName());   

	    cell = sheet.getRow(row1).getCell(0); // Asset's name
	    cell.setCellValue(asset.getName());
	    sheet.getRow(row1).setHeightInPoints(sheet.getDefaultRowHeightInPoints());
	    CellStyle style = workbook.createCellStyle();
		style.setWrapText(true);
//		style.setAutoSize(true);
		cell.setCellStyle(style);

//	    cell = sheet.getRow(row1).getCell(0); // Asset's name
//	    cell.setCellValue(asset.getName());   
	    
	    cell = sheet.getRow(row1).getCell(6); // Asset's Cost
		cell.setCellValue(asset.getAssetCost(currdate).doubleValue());
	    
	    cell = sheet.getRow(row1).getCell(17); // Inventory number
	    cell.setCellValue(asset.getValue());

	    int doctypeid = 0;
	    int invoiceid = 0;
	    int doctypeid2 = 0;
	    int invoiceid2 = 0;
	    Timestamp docdate = null;
	    String docno = null;
	    BigDecimal cost = null;
        BigDecimal accumdep = null;
    		    
	    
		pstmt = null;
		rs = null;
		String sql = "SELECT tl.A_Asset_Transaction_ID, t.C_DocType_ID,"
				+" t.C_Invoice_ID, t.DateAcct, t.DocumentNo, tl.AssetAmt, tl.DeprAmt \n "
				+" FROM A_Asset_TransactionLine AS tl \n"
				+"LEFT JOIN A_Asset_Transaction AS t "
				+"ON t.A_Asset_Transaction_ID = tl.A_Asset_Transaction_ID \n"
				+"WHERE tl.A_Asset_ID = "+asset.get_ID()
				+ " AND tl.IsActive = 'Y'";		
		try
		{
			pstmt = DB.prepareStatement(sql.toString(), m_trx);
			rs = pstmt.executeQuery();
			while (rs.next())
			{
			    if (rs.getRow()==1) {
			    	doctypeid = rs.getInt(2);
			    	invoiceid = rs.getInt(3);
			    } else {
			    	doctypeid2 = rs.getInt(2);
			    	invoiceid2 = rs.getInt(3);
			    	docdate = rs.getTimestamp(4);
			    	docno = rs.getString(5);
				    cost = rs.getBigDecimal(6);
			        accumdep = rs.getBigDecimal(7);
			    	
			    }	
			}
		}
		catch (Exception e)
		{
			log.log(Level.SEVERE, sql.toString(), e);
		}
		finally
		{
			DB.close(rs, pstmt);
		}
	    
	    if (doctypeid>0) {
	    	MDocType doctype = MDocType.get(m_ctx, doctypeid);
	    	cell = sheet.getRow(row1).getCell(26); // Doc name
	    	cell.setCellValue(doctype.get_Translation("Name"));
	    }
	    
	    if (invoiceid>0) {
	    	MInvoice invoice = MInvoice.get(m_ctx, invoiceid);
	    	cell = sheet.getRow(row1).getCell(30); // Doc number
	    	cell.setCellValue(invoice.getDocumentNo());
	    	cell = sheet.getRow(row1).getCell(34); // Doc date
	    	cell.setCellValue(new SimpleDateFormat("dd.MM.yyyy").format(invoice.getDateInvoiced()));
	    }

    	cell = sheet.getRow(row1).getCell(38); // Use life
    	cell.setCellValue(asset.getAcctValue("UseLifeMonths"));

    	cell = sheet.getRow(row1).getCell(41); // Accumulated depreciation
    	cell.setCellValue(asset.getAccumDepreciation(currdate).doubleValue());
	    
	    if (doctypeid2>0) {
	    	MDocType doctype = MDocType.get(m_ctx, doctypeid2);
	    	cell = sheet.getRow(row1).getCell(59); // Doc name
	    	cell.setCellValue(doctype.get_Translation("Name"));
	    }
	    
	    if (invoiceid2>0) {
	    	MInvoice invoice = MInvoice.get(m_ctx, invoiceid2);
	    	cell = sheet.getRow(row1).getCell(55); // Doc number + date
	    	cell.setCellValue(invoice.getDocumentNo() + " "+
	    			new SimpleDateFormat("dd.MM.yyyy").format(invoice.getDateInvoiced()));
	    } else {
	    	cell = sheet.getRow(row1).getCell(55); // Doc number + date
	    	if (docdate!=null) docno=docno+", "+
	    	   new SimpleDateFormat("dd.MM.yyyy").format(docdate);
	    	cell.setCellValue(docno);
	    	
	    }
	    
	    if (cost!=null) {
	    	cost=cost.subtract(accumdep);
	    	cell = sheet.getRow(row1).getCell(63); // Profit or loss
	    	cell.setCellValue(cost.doubleValue());
	    }
    	
    	cell = sheet.getRow(row1).getCell(67); // Balance cost
    	cell.setCellValue(asset.getAssetCost(currdate).subtract(
    			asset.getAccumDepreciation(currdate)).doubleValue());

    	cell = sheet.getRow(57).getCell(46); // Current date
    	cell.setCellValue(new SimpleDateFormat("dd").format(currdate));
    	cell = sheet.getRow(57).getCell(49);
    	cell.setCellValue(new SimpleDateFormat("MMMMM").format(currdate));
    	cell = sheet.getRow(57).getCell(55);
    	cell.setCellValue(new SimpleDateFormat("yyyy").format(currdate));
    	
    	
    	//--------------------------------------------------------------------------
    	//Write the output to a file
	    FileOutputStream out = new FileOutputStream(ffn);
	    workbook.write(out);
	    out.close();	    

		try {
			//Forces Excel to use the specified file as a template for the default workbook. 
			Runtime.getRuntime().exec("cmd /c start excel " + ffn+" /t");
		} 
		catch (Exception e)		{
			e.printStackTrace();
		}
	    
	    return Msg.translate(m_ctx, "Success");
	}

}